/*
 * Copyright (c) 2008 Skyon Technology Ltd. All rights reserved.
 * 
 * project: aps3.0 create: 2008-9-26 ����04:40:31 cvs: $Id:
 * org.eclipse.jdt.ui.prefs,v 1.3 2008/07/22 02:58:14 lijiafei Exp $
 */

package cn.batch.daemon.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import cn.batch.daemon.util.JdbcUtils;

import com.skyon.core.config.BaseJNDIEnv;
import com.skyon.core.jdbc.ConnectionProvider;

/**
 * 
 * 
 * @author xihn
 * @version $Revision: 1.1 $
 */
public class CompleteDayStore {

	private static Log logger = LogFactory.getLog(CompleteDayStore.class);

	private static ConnectionProvider connectionProvider;

	private static final String CODENAME_SQL = " select batch_id,CODE_NAME,CODE_ID from BATCH_CODE_LIST "
			+ " where batch_id in (select max(batch_id) from BATCH_CODE_LIST where CODE_ID='1' group by CODE_NAME) "
			+ " and BATCH_FLAG = '0' and CODE_NAME = (select min(CODE_NAME) from BATCH_CODE_LIST where batch_id in "
			+ " (select max(batch_id) from BATCH_CODE_LIST where CODE_ID='1' group by CODE_NAME) and BATCH_FLAG = '0')";

	private static final String RISKMONTHSQL = " select BATCH_ID,CODE_NAME,CODE_ID from BATCH_CODE_LIST where CODE_ID='5' and BATCH_FLAG = '2' or BATCH_FLAG = '3' order by CODE_NAME desc ";

	public static void init() {
		BaseJNDIEnv.init();
		connectionProvider = new JdbcConnectionOracleProvider();
	}
	/** 查询第二步任务已完成的最新的批次日期。 */
	public static Map<String, String> getRiskMonthFlagDate() throws Exception {
		init();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		Map<String, String> rMap = new HashMap<String, String>();

		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(RISKMONTHSQL);

			while (rs.next()) {
				rMap.put("BATCH_ID",
						rs.getBigDecimal(1) == null ? "" : rs.getBigDecimal(1).toString());
				rMap.put("CODE_NAME",
						rs.getString(2) == null ? "" : rs.getString(2));
				rMap.put("BATCH_TYPE",
						rs.getString(3) == null ? "" : rs.getString(3));
				break ; // 只取第一行数据
			}
		} catch (Exception e) {
			logger.error("查询风险预警按月待处理任务出错,QUERY SQL:" + RISKMONTHSQL, e);
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
		return rMap;
	}

	/**
	 * 
	 * 
	 * @return String
	 * @throws Exception
	 */
	public static Map<String, String> getCustCompleteDay() throws Exception {
		init();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		Map<String, String> rMap = new HashMap<String, String>();
		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(CODENAME_SQL);

			while (rs.next()) {
				rMap.put(
						"BATCH_ID",
						rs.getString(1) == null ? "" : String.valueOf(rs
								.getLong(1)));
				rMap.put("CODE_NAME",
						rs.getString(2) == null ? "" : rs.getString(2));
				rMap.put("BATCH_TYPE",
						rs.getString(3) == null ? "" : rs.getString(3));
			}
		} catch (Exception e) {
			logger.error("查询风险预警待处理任务出错,QUERY SQL:" + CODENAME_SQL, e);
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
		return rMap;
	}
	
	/**
	 * 
	 * 
	 * @return String
	 * @throws Exception
	 */
	public static Map<String, String> getRunBatchDate(String batchDate)
			throws Exception {
		init();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		Map<String, String> rMap = new HashMap<String, String>();
		String sql = "select batch_id,CODE_NAME,CODE_ID,BATCH_FLAG from BATCH_CODE_LIST where CODE_ID='5' and CODE_NAME = '"
				+ batchDate + "'";
		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);

			while (rs.next()) {
				rMap.put(
						"BATCH_ID",
						rs.getString(1) == null ? "" : String.valueOf(rs
								.getLong(1)));
				rMap.put("CODE_NAME",
						rs.getString(2) == null ? "" : rs.getString(2));
				rMap.put("BATCH_TYPE",
						rs.getString(3) == null ? "" : rs.getString(3));
				rMap.put("BATCH_FLAG",
						rs.getString(4) == null ? "" : rs.getString(4));
			}
		} catch (Exception e) {
			logger.error("查询风险预警待处理任务出错,QUERY SQL:" + CODENAME_SQL, e);
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
		return rMap;
	}

	/**
	 * 
	 * 
	 * @return String
	 * @throws Exception
	 */
	public static Map<String, String> getCustCompleteDayOfMonth(String batchDate)
			throws Exception {
		init();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		Map<String, String> rMap = new HashMap<String, String>();
		String sql = "select batch_id,CODE_NAME,CODE_ID from BATCH_CODE_LIST "
				+ " where batch_id = (select max(batch_id) from BATCH_CODE_LIST where CODE_ID='1'  and CODE_NAME = '"
				+ batchDate + "' group by CODE_NAME) ";
		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);

			while (rs.next()) {
				rMap.put(
						"BATCH_ID",
						rs.getString(1) == null ? "" : String.valueOf(rs
								.getLong(1)));
				rMap.put("CODE_NAME",
						rs.getString(2) == null ? "" : rs.getString(2));
				rMap.put("BATCH_TYPE",
						rs.getString(3) == null ? "" : rs.getString(3));
			}
		} catch (Exception e) {
			logger.error("查询风险预警待处理任务出错,QUERY SQL:" + CODENAME_SQL, e);
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
		return rMap;
	}
	/**
	 * 
	 * 
	 * @return String
	 * @throws Exception
	 
	public static Map<String, String> getCustCompleteDayMonthBatch()
			throws Exception {
		init();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		Map<String, String> rMap = new HashMap<String, String>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		String batchDate = sdf.format(DateUtil.getMonthLastDayDecreaseOneDay());
		String sql = "select BATCH_ID,CODE_NAME,CODE_ID from BATCH_CODE_LIST "
				+ " where CODE_ID = '5' and BATCH_FLAG='2' and CODE_NAME='"+batchDate+"'";
		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				rMap.put(
						"BATCH_ID",
						rs.getString(1) == null ? "" : String.valueOf(rs
								.getLong(1)));
				rMap.put("CODE_NAME",
						rs.getString(2) == null ? "" : rs.getString(2));
				rMap.put("CODE_ID",
						rs.getString(3) == null ? "" : rs.getString(3));
			}
		} catch (Exception e) {
			logger.error("查询风险预警待处理任务出错,QUERY SQL:" + CODENAME_SQL, e);
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
		return rMap;
	}
	*/
	/** 获得指定的任务标识记录。
	 * 主要用于判断该任务标识是否存在。
	 */
	public static Map<String, String> getBatchCode(String codeId, String codeName, String batchFlag)
			throws Exception {
		init();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		Map<String, String> rMap = new HashMap<String, String>();
		
		String sql = new StringBuilder()
						.append("select BATCH_ID, CODE_NAME, CODE_ID from BATCH_CODE_LIST ")
						.append(" where CODE_ID = ").append(codeId)
						.append(" and CODE_NAME = ").append(codeName)
						.append(" and BATCH_FLAG = ").append(batchFlag).toString();
		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);

			while (rs.next()) {
				rMap.put(
						"BATCH_ID",
						rs.getString(1) == null ? "" : String.valueOf(rs
								.getLong(1)));
				rMap.put("CODE_NAME",
						rs.getString(2) == null ? "" : rs.getString(2));
				rMap.put("BATCH_TYPE",
						rs.getString(3) == null ? "" : rs.getString(3));
			}
		} catch (Exception e) {
			logger.error("查询风险预警待处理任务出错,QUERY SQL:" + CODENAME_SQL, e);
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
		return rMap;
	}

	public static void insertBatchCodeList(String codeId, String codeName, String batchFlag, String desp)
			throws SQLException {
		init();
		Connection connection = null;
		Statement statement = null;
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" INSERT INTO BATCH_CODE_LIST (BATCH_ID,CODE_ID,CODE_NAME,BATCH_FLAG,DESP) ")
				  .append(" VALUES ")
				  .append(" (SEQ_BATCH_CODE_LIST_ID.Nextval, ")
				  .append("'").append(codeId).append("', ")
				  .append("'").append(codeName).append("', ")
				  .append("'").append(batchFlag).append("', ")
				  .append("'").append(desp).append("' ) ");

		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			statement.executeUpdate(sqlBuilder.toString());
		} catch (SQLException sqle) {
			connection.rollback();
			logger.error("新增风险预警按月处理任务标志出错， SQL: " + sqlBuilder.toString(), sqle);
			throw sqle;
		} finally {
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
	}

	/**
	 * 
	 * 
	 * @param comDay
	 * 
	 * @return int
	 * @throws SQLException
	 * @throws Exception
	 */
	public static void updateBatchCodeList(String batchID, String batch_flag)
			throws SQLException {
		init();
		Connection connection = null;
		Statement statement = null;
		String sql = "UPDATE BATCH_CODE_LIST SET BATCH_FLAG='" + batch_flag
				+ "' WHERE BATCH_ID='" + batchID + "'";

		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			statement.executeUpdate(sql);
		} catch (SQLException sqle) {
			connection.rollback();
			logger.error("更新风险预警待处理任务标志出错,UPDATE SQL:" + sql, sqle);
			throw sqle;
		} finally {
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
	}
	
	/**
	 * 
	 * 
	 * @param batchID
	 * 
	 * @throws SQLException
	 * @throws Exception
	 */
	public static void deleteBatchCodeList(String batchID)
			throws SQLException {
		init();
		Connection connection = null;
		Statement statement = null;
		String sql = "DELETE FROM  BATCH_CODE_LIST WHERE BATCH_ID='" + batchID + "'";

		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			statement.executeUpdate(sql);
		} catch (SQLException sqle) {
			connection.rollback();
			logger.error("删除风险预警批次处理任务标志出错,DELETE SQL:" + sql, sqle);
			throw sqle;
		} finally {
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
	}
	/**
	 * 更新任务状态标识。
	 * 如果有第一步已完成的标识记录，则更新为第二步已完成的状态。
	 * 否则，直接插入第二步已完成的状态标识记录。
	 * @param codeId
	 * @param batchDate
	 * @param batchFlag
	 * @param desp
	 * @throws Exception
	 */
	public static void saveOrUpdateBatchCodeList(String codeId,
			String batchDate, String batchFlag, String desp) throws Exception {
		Map<String, String> map = getBatchCode(codeId, batchDate, "0") ;
		if (map.size() > 0) {
			updateBatchCodeList(map.get("BATCH_ID"), batchFlag);
		} else {
			insertBatchCodeList(codeId, batchDate, batchFlag, desp);
		}
	}
}
